#! /usr/bin/env python
# -*- coding: utf-8 -*-
# __author__ = "Q1mi"
# Date: 2020/8/12
import codecs
import csv
from datetime import datetime, time
import pymysql
from rest_framework import exceptions
from collections import namedtuple
from django.conf import settings

from Apps.Toad import models


class Connection():
    '''自定义数据库查询，
    数据库连接的公共类，
    提供连接数据库，查询，删除语句等操作
    '''

    def __init__(self,dbid=None, dbName=None,host=None,user=None,password=None,port=None):
        self.currentConn = None
        self.dbid = dbid
        self.host = host
        self.port = port
        self.user =user
        self.password = password
        self.dbName = dbName
        self.charset = "utf8mb4"
        self.resultlist = []

    def open(self):
        try:
            print(self.host,self.user,self.port,self.password,self.dbName)
            conn = pymysql.connect(
                host=self.host,
                user=self.user,
                port=int(self.port),
                password=self.password,
                db=self.dbName,
                charset=self.charset,
            )
        except pymysql.err.OperationalError as e:
                raise exceptions.APIException(f'''数据库连接失败！{str(e)}''')
        self.currentConn = conn  # 数据库连接完成
        self.cursor = self.currentConn.cursor()  # 游标，用来执行数据库

        # 把多条sql拆分成单条，放进sqllist里面

    def spliteSql(self, sql):
        sqllist = sql.split(';')
        return sqllist[0:-1]
        # 最后面会多一条空值

    def execSql(self, sql: str,limit:50,userid:0,title:'', closeConn=True) -> list:
        '''执行sql,支持执行多条sql语句。'''
        self.open()
        isok=1
        try:
            sqllist = self.spliteSql(sql)  # 先处理传入的sql语句
            alllog=''
            with self.cursor as my_cursor:
                i=0
                starttime = datetime.now()  # 获得当前时间
                for v in sqllist:
                    onestarttime = datetime.now()  # 获得当前时间
                    i+=1
                    try:
                        v=v.strip().lower()
                        if v.startswith('select') :
                            sqlcnt=f"""select count(1) cnt from ({v}) t;"""
                            my_cursor.execute(sqlcnt)  # 执行sql语句
                            dt_cnt=my_cursor.fetchall()
                            # 判断是否包含limit
                            if v.rfind('limit')<0 :
                                v_sql=f"""{v} limit {limit}"""
                            my_cursor.execute(v_sql)  # 执行sql语句
                            self.resultlist.append({"index":i,"status":True,"msg":"执行成功！","dbid":self.dbid,"sqlbody":v,"total":dt_cnt[0][0],"result":self.dictfetchall(my_cursor)}) # 获取数据
                            oneendtime = datetime.now()  # 获取当前时间
                            onedurn = (oneendtime - onestarttime).total_seconds()  # 两个时间差，并以秒显示出来
                            alllog +="{0}.查询数据成功,数据总行数:{1},执行时间秒数:{2};".format(i,dt_cnt[0][0],onedurn)
                        elif v.startswith('show'):
                            my_cursor.execute(v)  # 执行sql语句
                            self.resultlist.append(
                                {"index": i, "status": True, "msg": "执行成功！", "dbid": self.dbid, "sqlbody": v,
                                 "total": 0, "result": self.dictfetchall(my_cursor)})  # 获取数据
                            oneendtime = datetime.now()  # 获取当前时间
                            onedurn = (oneendtime - onestarttime).total_seconds()  # 两个时间差，并以秒显示出来
                            alllog += "{0}.执行语句成功,执行时间秒数:{1};".format(i,onedurn)
                        else:
                            my_cursor.execute(v)  # 执行sql语句
                           # self.resultlist.append({"index":i,"status":True,"msg":"执行成功！","dbid":self.dbid,"sqlbody":v,"total":0,"result":{}}) # 获取数据my_cursor.fetchall()
                            oneendtime = datetime.now()  # 获取当前时间
                            onedurn = (oneendtime - onestarttime).total_seconds()  # 两个时间差，并以秒显示出来
                            alllog += "{0}.执行语句成功,执行时间秒数:{1};".format(i, onedurn)
                        self.currentConn.commit()  # 提交
                    except Exception as ex:
                        isok=0
                        #self.resultlist.append({"index": i, "status": False, "msg": str(ex), "result": {}})  # 获取数据
                        oneendtime = datetime.now()  # 获取当前时间
                        onedurn = (oneendtime - onestarttime).total_seconds()  # 两个时间差，并以秒显示出来
                        alllog += "{0}.执行语句失败,{1},执行时间秒数:{2};".format(i,str(ex),onedurn)
                        break

                endtime = datetime.now()  # 获取当前时间
                durn = (endtime - starttime).total_seconds()  # 两个时间差，并以秒显示出来
                self.savetoadlog(operuserid=userid, opertype='数据执行', datacount=0, sqlbody=sql, dbid=self.dbid,
                                 mess=alllog, runtime=durn,title=title,status=isok)

            if self.currentConn:
                self.close()
            return (self.resultlist,alllog)
        except Exception as ex:
            raise exceptions.APIException(f'''数据库执行失败！{str(ex)}''')
        # def execProc(self):

    #toas执行日志
    def savetoadlog(self,operuserid,opertype,datacount,sqlbody,dbid,mess,runtime,title,status):
        try:
            tlog={}
            tlog['user_id']=operuserid
            tlog['opertype'] = opertype
            tlog['datacount'] = datacount
            tlog['sqlbody'] = sqlbody
            tlog['db_id'] = dbid
            tlog['mess'] = mess
            tlog['runtime'] = runtime
            tlog['title']=title
            tlog['status'] = int(status)
            coll = models.StToadLog.objects.create(**tlog)
            coll.save()
        except Exception as ex:
            print('保存日志异常！',ex)

    def dictfetchall(self,cursor):
        "Return all rows from a cursor as a dict"
        columns = [col[0] for col in cursor.description]
        data=[
            dict(zip(columns, row))
            for row in cursor.fetchall()
        ]
        return {"head":columns,"data":data}


    def namedtuplefetchall(self,cursor):
        "Return all rows from a cursor as a namedtuple"
        desc = cursor.description
        nt_result = namedtuple('Result', [col[0] for col in desc])
        return [nt_result(*row) for row in cursor.fetchall()]

    def close(self):  # 关闭连接
        if self.cursor:
            self.cursor.close()
        self.currentConn.close()

    def exportData(self, sql: str, userid: 0, closeConn=True) -> list:
        '''执行sql,支持执行多条sql语句。'''
        self.open()
        file_name = "数据导出" + datetime.now().strftime("%Y%m%d%H%M%S") + ".csv"
        file_path=settings.UPLOAD_PATH+file_name
        try:
            with self.cursor as my_cursor:
                my_cursor.execute(sql)
                file_csv = codecs.open(file_path, 'w+')  # 追加
                writer = csv.writer(file_csv, delimiter=',', quotechar=' ', quoting=csv.QUOTE_MINIMAL)
                columns = [col[0] for col in my_cursor.description]
                writer.writerow(columns)
                for data in my_cursor:
                    writer.writerow(data)
                if self.currentConn:
                    self.close()
            return file_name
        except Exception as ex:
            if self.currentConn:
                self.close()
            raise exceptions.APIException(f'''数据库执行失败！{str(e)}''')
            # def execProc(self):



